package com.coderman.generateSqlBatchTools.service.impl;

import com.coderman.generateSqlBatchTools.bean.ColumnBean;
import com.coderman.generateSqlBatchTools.service.GenerateHelper;
import com.coderman.generateSqlBatchTools.service.IGenerateSqlTools;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;

/**
 * Created by ${fanchunshuai} on 2017-4-8.
 *
 * @version: V1.0
 * @Desc: 实现基于xls格式的sql生成服务接口
 * @Copyright (c) 2017 58到家-版权所有
 */
public class GenerateSqlToolsXLSImpl implements IGenerateSqlTools {

    private static Logger logger = Logger.getLogger(GenerateSqlToolsXLSImpl.class);

    private static Workbook rwb = null;
    private static InputStream is = null;

    private  Sheet st = null;
    @Override
    public boolean generateInsertBatchSql(String excelFilePath, int outDataType, String outPath, int fromIndex, Map<String, ColumnBean> columnBeanMap, String tableName) {
        int rows;
        Sheet st;
        try {
            st = readExcelByJXL(excelFilePath);
            rows = st.getRows();
            //参数校验
            if(!GenerateHelper.checkParams(fromIndex,rows,tableName,columnBeanMap)){
                return false;
            }
            StringBuilder builder = new StringBuilder("insert into ");
            builder.append(tableName);
            StringBuilder sqlBuilder = generateSql(st,columnBeanMap,fromIndex);
            builder.append(sqlBuilder);
            //对生成的sql内容进行处理
            return GenerateHelper.resultDataManage(outDataType, builder, outPath);
        } catch (Exception e) {
            logger.error("is't failed ,the process to generate sqlbatch occurs exception ",e);
        }finally {
            close();
        }
        return false;
    }

    /**
     * 关闭资源
     */
    private  void close(){

        try {
            //关闭资源
            if(rwb != null){
                rwb.close();
            }
            if(is != null){
                is.close();
            }
        } catch (IOException e) {
            logger.error("it's success,but close resource has error... ",e);
        }
    }


    /**
     * 根据excel sheet内容拼接sql部分内容
     * @param st
     * @param columnBeanMap
     * @param fromIndex
     * @return
     * @throws Exception
     */
    private  StringBuilder generateSql(Sheet st,Map<String,ColumnBean> columnBeanMap,int fromIndex)throws Exception{
        StringBuilder valuebuilder = new StringBuilder(" values ");
        StringBuilder columnBuilder = new StringBuilder("(");

        for (int k = fromIndex; k < st.getRows(); k++) {
            Iterator<Map.Entry<String,ColumnBean>> entryIterator = columnBeanMap.entrySet().iterator();
            StringBuilder builder = new StringBuilder("(");
            for (Map.Entry<String, ColumnBean> entry : columnBeanMap.entrySet()) {
                ColumnBean columnBean = entry.getValue();
                if(k == fromIndex){
                    columnBuilder.append(columnBean.getColumnName());
                    columnBuilder.append(", ");
                }
                String value =  st.getCell(columnBean.getIndex(), k).getContents().trim();

                if(StringUtils.isNotBlank(value)){
                    if(columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()){
                        long lValue = Long.parseLong(value);
                        builder.append(lValue);
                        builder.append(", ");
                    }
                    if(columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()){
                        int iValue = Integer.parseInt(value);
                        builder.append(iValue);
                        builder.append(", ");
                    }
                    if(columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()){
                        boolean bValue = Boolean.parseBoolean(value);
                        builder.append(bValue);
                        builder.append(", ");
                    }
                    if(columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()){
                        builder.append("'");
                        builder.append(value);
                        builder.append("' ,");
                    }
                }

            }

            valuebuilder.append(builder.substring(0,builder.length() - 2));
            valuebuilder.append("),\n");
        }


        String columnStr = columnBuilder.substring(0,columnBuilder.length() - 2)+")";
        String valueStr = valuebuilder.substring(0,valuebuilder.length() - 2);
        return new StringBuilder().append(columnStr).append("  ").append(valueStr);
    }

    /**
     * 公用通过JXL读取excel的方法返回 sheet对象
     * @param filePath  文件地址
     * @return
     */
    private static Sheet readExcelByJXL(String filePath){

        Sheet st = null;
        try {
            is = new FileInputStream(filePath);
            rwb = Workbook.getWorkbook(is);

            st = rwb.getSheet(0);
        }catch (Exception e){
            e.printStackTrace();
            logger.error("read excel by jxl is error ",e);
        }
        return st;
    }


    @Override
    public boolean generateDeleteBatchSql(String excelFilePath, int outDataType, String outPath, int fromIndex, Map<String, ColumnBean> columnBeanMap, String tableName) {
        int rows;
        Sheet st;
        try {
            st = readExcelByJXL(excelFilePath);
            rows = st.getRows();
            StringBuilder batchBuilder = new StringBuilder();
            //参数检查
            if(!GenerateHelper.checkParams(fromIndex,rows,tableName,columnBeanMap)){
                return false;
            }
            for (int k = fromIndex; k < st.getRows(); k++) {
                StringBuilder sqlBuilder = new StringBuilder("delete from ");
                sqlBuilder.append(tableName);
                sqlBuilder.append(" where ");
                Iterator<Map.Entry<String,ColumnBean>> entryIterator = columnBeanMap.entrySet().iterator();

                for (Map.Entry<String, ColumnBean> entry : columnBeanMap.entrySet()) {
                    ColumnBean columnBean = entry.getValue();

                    String value =  st.getCell(columnBean.getIndex(), k).getContents().trim();

                    if(StringUtils.isNotBlank(value)){
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            long lValue = Long.parseLong(value);
                            sqlBuilder.append(lValue);
                            sqlBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            int iValue = Integer.parseInt(value);
                            sqlBuilder.append(iValue);
                            sqlBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            boolean bValue = Boolean.parseBoolean(value);
                            sqlBuilder.append(bValue);
                            sqlBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            sqlBuilder.append("'");
                            sqlBuilder.append(value);
                            sqlBuilder.append("' and ");
                        }
                    }
                }
                String sql = sqlBuilder.substring(0,sqlBuilder.length() - 5);
                sqlBuilder = new StringBuilder(sql);
                sqlBuilder.append(";\n");
                batchBuilder.append(sqlBuilder);
            }

            //对生成的sql语句进行处理
            return GenerateHelper.resultDataManage(outDataType,batchBuilder,outPath);

        } catch (Exception e) {
            logger.error("is't failed ,the process to generate sqlbatch occurs exception ",e);

        }finally {
            close();
        }
        return false;

    }

    @Override
    public boolean generateUpdateBatchSql(String excelFilePath, int outDataType, String outPath, int fromIndex,
                                          Map<String, ColumnBean> columnBeanMap,Map<String,ColumnBean> whereBeanMap, String tableName) {
        int rows;
        Sheet st;
        try {
            st = readExcelByJXL(excelFilePath);
            rows = st.getRows();
            StringBuilder batchBuilder = new StringBuilder();

            //参数检查
            if(!GenerateHelper.checkParams(fromIndex,rows,tableName,columnBeanMap)){
                return false;
            }

            for (int k = fromIndex; k < st.getRows(); k++) {
                StringBuilder sqlBuilder = new StringBuilder("update ");
                sqlBuilder.append(tableName);
                sqlBuilder.append(" set ");

                for (Map.Entry<String, ColumnBean> entry : columnBeanMap.entrySet()) {
                    ColumnBean columnBean = entry.getValue();

                    String value =  st.getCell(columnBean.getIndex(), k).getContents().trim();

                    if(StringUtils.isNotBlank(value)){
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            long lValue = Long.parseLong(value);
                            sqlBuilder.append(lValue);
                            sqlBuilder.append(" , ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            int iValue = Integer.parseInt(value);
                            sqlBuilder.append(iValue);
                            sqlBuilder.append(" , ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            boolean bValue = Boolean.parseBoolean(value);
                            sqlBuilder.append(bValue);
                            sqlBuilder.append(" , ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            sqlBuilder.append("'");
                            sqlBuilder.append(value);
                            sqlBuilder.append("' , ");
                        }
                    }
                }

                StringBuilder whereBuilder = new StringBuilder(" where ");
                for (Map.Entry<String, ColumnBean> entry : whereBeanMap.entrySet()) {
                    ColumnBean columnBean = entry.getValue();

                    String value =  st.getCell(columnBean.getIndex(), k).getContents().trim();

                    if(StringUtils.isNotBlank(value)){
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            long lValue = Long.parseLong(value);
                            whereBuilder.append(lValue);
                            whereBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            int iValue = Integer.parseInt(value);
                            whereBuilder.append(iValue);
                            whereBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            boolean bValue = Boolean.parseBoolean(value);
                            whereBuilder.append(bValue);
                            whereBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            whereBuilder.append("'");
                            whereBuilder.append(value);
                            whereBuilder.append("' and ");
                        }
                    }
                }

                String sql = sqlBuilder.substring(0,sqlBuilder.length() - 2);
                sqlBuilder = new StringBuilder(sql);
                String whereStr = whereBuilder.substring(0,whereBuilder.length() - 5);
                sqlBuilder.append(" ");
                sqlBuilder.append(whereStr);
                sqlBuilder.append(";\n");
                batchBuilder.append(sqlBuilder);
            }
            //对生成的sql语句进行处理
            return GenerateHelper.resultDataManage(outDataType,batchBuilder,outPath);

        } catch (Exception e) {
            e.printStackTrace();
            logger.error("is't failed ,the process to generate sqlbatch occurs exception ",e);

        }finally {
            close();
        }
        return false;
    }
}
